﻿// ============================================================================
//     _____ __  _      __            _       __      ____
//    / ___// /_(_)____/ /_____  ____| |     / /___ _/ / /
//    \__ \/ __/ / ___/ //_/ _ \/ ___/ | /| / / __ `/ / /
//   ___/ / /_/ / /__/ ,< /  __/ /   | |/ |/ / /_/ / / /
//  /____/\__/_/\___/_/|_|\___/_/    |__/|__/\__,_/_/_/
//
// Copyright (C) by daxnet
// https://gitee.com/daxnet/stickerwall
// MIT License
// ============================================================================

using System.Text;
using Dapper;
using Microsoft.Extensions.Logging;
using Npgsql;
using Stickerwall.Common;
using Stickerwall.Common.Models;

namespace Stickerwall.DataAccessors.PgSQL;

public class PgSqlDataAccessor(string? connectionString, ILogger<PgSqlDataAccessor> logger) : IDataAccessor
{

    #region Private Fields

    private const string StickersTableName = "stickers";

    #endregion Private Fields

    #region Public Methods

    public async Task<int> CreateStickerAsync(string tenantId, Sticker sticker, CancellationToken cancellationToken = default)
    {
        var sql = $@"INSERT INTO {CreateStickerTableName(tenantId)} (""Title"", ""Content"", ""DateCreated"", ""UserId"") VALUES (@Title, @Content, @DateCreated, @UserId) RETURNING ""Id""";
        logger.LogDebug($"CreateStickerAsync SQL: {sql}");
        await using var sqlConnection = new NpgsqlConnection(connectionString);
        return await sqlConnection.ExecuteScalarAsync<int>(sql, sticker);
    }

    public async Task DeleteStickerByIdAsync(string tenantId, int id, CancellationToken cancellationToken = default)
    {
        var sql = $@"DELETE FROM {CreateStickerTableName(tenantId)} WHERE ""Id""=@id";
        logger.LogDebug($"DeleteStickerByIdAsync SQL: {sql}");
        await using var sqlConnection = new NpgsqlConnection(connectionString);
        await sqlConnection.ExecuteScalarAsync(sql, new { id });
    }

    public async Task<Sticker?> GetStickerByIdAsync(string tenantId, int id, CancellationToken cancellationToken = default)
    {
        var sql = $@"SELECT * FROM {CreateStickerTableName(tenantId)} WHERE ""Id""=@id";
        logger.LogDebug($"GetStickerByIdAsync SQL: {sql}");
        await using var sqlConnection = new NpgsqlConnection(connectionString);
        return await sqlConnection.QuerySingleOrDefaultAsync<Sticker>(sql, new { id });
    }

    public async Task<PagedEntity<Sticker>> GetStickersAsync(string tenantId,
        string filterExpression = "",
        string sortExpression = "DateCreated",
        SortOrder sortOrder = SortOrder.Descending, int pageSize = 25, int pageNumber = 1,
        CancellationToken cancellationToken = default) =>
        await ReadPaginatedStickersAsync(tenantId, filterExpression, sortExpression, sortOrder, pageSize,
            pageNumber);

    public async Task UpdateStickerAsync(string tenantId, int id, Sticker sticker, CancellationToken cancellationToken = default)
    {
        var sql =
            $@"UPDATE {CreateStickerTableName(tenantId)} SET ""Title"" = @title, ""Content"" = @content, ""DateModified"" = @dateModified WHERE ""Id""=@id";
        logger.LogDebug($"UpdateStickerAsync SQL: {sql}");
        await using var sqlConnection = new NpgsqlConnection(connectionString);
        await sqlConnection.ExecuteScalarAsync(sql, new
        {
            id,
            title = sticker.Title,
            content = sticker.Content,
            dateModified = DateTime.UtcNow
        });
    }

    public async Task<(bool, string?)> CheckDatabaseTableAsync(CancellationToken cancellationToken = default)
    {
        var sql = "SELECT \"Id\" FROM public.audits ORDER BY \"Id\" FETCH FIRST 1 ROWS ONLY";
        await using var sqlConnection = new NpgsqlConnection(connectionString);
        try
        {
            await sqlConnection.ExecuteScalarAsync(sql);
            return (true, null);
        }
        catch (Exception e)
        {
            logger.LogWarning(e, "Database table test didn't run successfully.");
            return (false, e.ToString());
        }
    }

    #endregion Public Methods

    #region Private Methods

    private static string CreateStickerTableName(string tenantId) => $"{tenantId}.{StickersTableName}";

    private async Task<PagedEntity<Sticker>> ReadPaginatedStickersAsync(string tenantId, string filterExpression = "",
        string sortExpression = "Id", SortOrder sortOrder = SortOrder.Ascending, int pageSize = 25, int pageNumber = 1)
    {
        var sqlBuilder = new StringBuilder();
        sqlBuilder.Append($@"SELECT *, COUNT(*) OVER() AS ""TotalCount"" FROM {CreateStickerTableName(tenantId)} ");
        if (!string.IsNullOrEmpty(filterExpression))
        {
            sqlBuilder.Append($@"WHERE {filterExpression} ");
        }

        if (!string.IsNullOrEmpty(sortExpression))
        {
            sqlBuilder.Append($@"ORDER BY ""{sortExpression}"" ");
            if (sortOrder == SortOrder.Descending)
            {
                sqlBuilder.Append("DESC ");
            }
        }

        var offset = (pageNumber - 1) * pageSize;
        sqlBuilder.Append($"OFFSET {offset} LIMIT {pageSize}");
        await using var connection = new NpgsqlConnection(connectionString);
        var sql = sqlBuilder.ToString();
        logger.LogDebug($"Paginated query SQL: {sql}");
        var stickers = await connection.QueryAsync(sql);
        var result = new PagedEntity<Sticker>();
        var firstTaken = false;
        foreach (var sticker in stickers)
        {
            if (!firstTaken)
            {
                var totalCount = sticker.TotalCount;
                var totalPages = Convert.ToInt32(Math.Ceiling(totalCount * 1.0F / pageSize));
                result.PageIndex = pageNumber;
                result.PageSize = pageSize;
                result.TotalPages = totalPages;
                result.TotalCount = totalCount;
                firstTaken = true;
            }

            result.Items.Add(new Sticker(sticker.UserId, sticker.Title, sticker.Content)
            {
                DateCreated = sticker.DateCreated,
                DateModified = sticker.DateModified,
                Id = sticker.Id,
            });
        }

        return result;
    }

    #endregion Private Methods

}